﻿using System;
using System.Linq;
using System.IO.Compression;
using System.IO;
using System.Data.OleDb;
using System.Data;
using SupermarketModel;
using Supermarket.Data;

namespace Supermarket.Client
{
    static class ExcelFromZip
    {
        public static void ImportSales()
        {
            string zipPath = @"..\..\..\Sample-Sales-Reports.zip";
            string unzipDirectory = @"..\..\..\ExtractedZip";
            ExcelFromZip.UnzipFile(zipPath, unzipDirectory);
            ExcelFromZip.FindExcelFiles(unzipDirectory);
            Directory.Delete(unzipDirectory, true);
        }

        private static void FindExcelFiles(string directoryPath)
        {
            var excelFiles = Directory.EnumerateFiles(directoryPath, "*.xls");
            foreach (var file in excelFiles)
            {
                ReadExcelsFromDirectory(file);
            }

            var directories = Directory.EnumerateDirectories(directoryPath);
            foreach (var directory in directories)
            {
                try
                {
                    FindExcelFiles(directory);
                }
                catch (Exception e)
                {
                    Console.WriteLine(e.Message);
                }
            }
        }

        private static void ReadExcelsFromDirectory(string filePath)
        {
            DataTable dt = new DataTable("newtable");

            using (OleDbConnection connection = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=Excel 12.0;"))
            {
                connection.Open();
                string selectSql = @"SELECT * FROM [Sales$]";
                using (OleDbDataAdapter adapter = new OleDbDataAdapter(selectSql, connection))
                {
                    adapter.FillSchema(dt, SchemaType.Source);
                    adapter.Fill(dt);
                }
                connection.Close();
            }

            var sqlDb = new SupermarketContext();
            using (sqlDb)
            {
                var datePath = filePath.Split('\\');

                var newMarket = new Market();

                newMarket.Name = dt.Rows[0][0].ToString();

                var selectedMarket = sqlDb.Markets.Where(x => x.Name == newMarket.Name).Select(x => x.Id).FirstOrDefault();
                if (selectedMarket == 0)
                {
                    sqlDb.Markets.Add(newMarket);
                    sqlDb.SaveChanges();
                    selectedMarket = sqlDb.Markets.Where(x => x.Name == newMarket.Name).Select(x => x.Id).FirstOrDefault();
                }

                for (int i = 2; i < dt.Rows.Count - 2; i++)
                {
                    var report = dt.Rows[i];
                    var newReport = new SalesReport();

                    newReport.ProductId = int.Parse(report[0].ToString());
                    newReport.Quantity = int.Parse(report[1].ToString());
                    newReport.UnitPrice = decimal.Parse(report[2].ToString());
                    newReport.Sum = decimal.Parse(report[3].ToString());
                    newReport.MarketId = selectedMarket;
                    newReport.ReportDate = DateTime.Parse(datePath[datePath.Length - 2]);
                    sqlDb.SalesReports.Add(newReport);
                }
                sqlDb.SaveChanges();
            }
        }

        private static void UnzipFile(string zipPath, string unzipDirectory)
        {
            ZipFile.ExtractToDirectory(zipPath, unzipDirectory);
        }
    }
}
